const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs').promises;

let db;

let _projectRoot;

// 初始化主数据库
function initDatabase(projectRoot) {
    _projectRoot = projectRoot;
    const mainDbPath = path.join(projectRoot, 'cases.db');

    return new Promise((resolve, reject) => {
        db = new sqlite3.Database(mainDbPath, (err) => {
            if (err) {
                console.error('Cannot open main database:', err.message);
                reject(err);
            } else {
                console.log('Connected to main database:', mainDbPath);

                // 创建案件表
                const createTableSQL = `
          CREATE TABLE IF NOT EXISTS cases (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            case_name TEXT NOT NULL,
            case_status TEXT NOT NULL,
            case_number TEXT UNIQUE NOT NULL,
            created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            case_start_time DATETIME,
            password TEXT,
            description TEXT,
            closed_status INTEGER DEFAULT 0,
            db_path TEXT  -- 存储独立数据库文件路径
          )`;

                db.run(createTableSQL, (err) => {
                    if (err) {
                        console.error('Failed to create cases table:', err.message);
                        reject(err);
                    } else {
                        console.log('Cases table created successfully or already exists');
                        // 检查并添加缺失的列
                        addMissingColumns();
                        resolve(db);
                    }
                });
            }
        });
    });
}

// 获取主数据库连接
function getDb() {
    return db;
}

// 检查并添加缺失的列
function addMissingColumns() {
    if (!db) return;
    db.all("PRAGMA table_info(cases)", [], (err, columns) => {
        if (err) {
            console.error('Failed to get table structure:', err.message);
            return;
        }

        const columnNames = columns.map(col => col.name);

        // 添加db_path列（如果不存在）
        if (!columnNames.includes('db_path')) {
            db.run(`ALTER TABLE cases ADD COLUMN db_path TEXT`, (err) => {
                if (!err) console.log('Successfully added db_path column');
                else console.error('Failed to add db_path column:', err);
            });
        }
    });
}

// 获取案件独立数据库连接
function getCaseDatabase(caseId) {
    return new Promise((resolve, reject) => {
        if (!db) {
            reject(new Error('Main database not initialized'));
            return;
        }
        // 查询案件的数据库路径和编号
        const sql = 'SELECT db_path, case_number FROM cases WHERE id = ?';
        db.get(sql, [caseId], async (err, row) => {
            if (err) {
                reject(err);
                return;
            }

            if (!row) {
                reject(new Error('案件不存在'));
                return;
            }

            let dbPath = row.db_path;

            // 如果路径不存在，尝试自动修复
            if (!dbPath) {
                console.log(`Case ${caseId} missing db_path, attempting to repair...`);
                try {
                    const casesDir = path.join(_projectRoot, 'cases');
                    const expectedPath = path.join(casesDir, `${row.case_number}_id${caseId}.db`);

                    // 检查文件是否存在
                    try {
                        await fs.access(expectedPath);
                        console.log(`Found existing database at ${expectedPath}`);
                        dbPath = expectedPath;
                    } catch (e) {
                        console.log(`Database file not found, creating new one at ${expectedPath}`);
                        // 文件不存在，创建新的
                        dbPath = await createCaseDatabase(_projectRoot, row.case_number, caseId);
                    }

                    // 更新主数据库中的路径
                    await new Promise((resolveUpdate, rejectUpdate) => {
                        db.run('UPDATE cases SET db_path = ? WHERE id = ?', [dbPath, caseId], (err) => {
                            if (err) {
                                console.error('Failed to update db_path:', err);
                                // 即使更新失败，我们也可以继续使用这个路径，只是下次还得修复
                            } else {
                                console.log('Updated cases table with new db_path');
                            }
                            resolveUpdate();
                        });
                    });

                } catch (repairError) {
                    console.error('Failed to repair case database:', repairError);
                    reject(new Error('案件数据库路径未找到且无法自动修复'));
                    return;
                }
            }

            // 创建案件的独立数据库实例
            const caseDb = new sqlite3.Database(dbPath, (err) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(caseDb);
                }
            });
        });
    });
}

// 为新案件创建独立数据库文件
async function createCaseDatabase(projectRoot, caseNumber, caseId) {
    try {
        const casesDir = path.join(projectRoot, 'cases');

        // 创建案件目录
        await fs.mkdir(casesDir, { recursive: true });

        // 创建独立数据库文件
        const dbPath = path.join(casesDir, `${caseNumber}_id${caseId}.db`);

        // 创建数据库并初始化表结构
        const caseDb = new sqlite3.Database(dbPath);

        // 创建案件相关的表结构（示例）
        const tablesSQL = [
            `CREATE TABLE IF NOT EXISTS evidence (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        file_path TEXT,
        created_time DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
            `CREATE TABLE IF NOT EXISTS persons (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        role TEXT,
        description TEXT
      )`,
            `CREATE TABLE IF NOT EXISTS timeline (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        event_date DATETIME,
        event_title TEXT NOT NULL,
        event_description TEXT
      )`,
            // 添加导入日志表
            `CREATE TABLE IF NOT EXISTS import_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        case_id INTEGER,
        file_name TEXT,
        file_hash TEXT,
        data_type TEXT,
        import_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(case_id, file_hash)
      )`,
            // 修改银行流水表，为记录哈希添加唯一索引
            `CREATE TABLE IF NOT EXISTS bank_transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        case_id INTEGER,
        transaction_time DATETIME,
        account_number TEXT,
        counterparty_account TEXT,
        transaction_amount REAL,
        transaction_type TEXT,
        balance_after REAL,
        remarks TEXT,
        customer_name TEXT,
        id_number TEXT,
        account_card TEXT,
        phone_number TEXT,
        account_name TEXT,
        bank_name TEXT,
        counterparty_id_number TEXT,
        counterparty_bank_code TEXT,
        counterparty_bank_name TEXT,
        transaction_branch_name TEXT,
        teller_number TEXT,
        debit_credit_flag TEXT,
        counterparty_name TEXT,
        counterparty_card TEXT,
        extra_data TEXT,
        record_hash TEXT,  -- 用于去重的哈希值
        created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(case_id, record_hash)  -- 联合唯一索引
      )`
        ];

        // 执行建表语句
        for (const sql of tablesSQL) {
            await new Promise((resolve, reject) => {
                caseDb.run(sql, (err) => {
                    if (err) reject(err);
                    else resolve();
                });
            });
        }

        caseDb.close();

        return dbPath;
    } catch (error) {
        console.error('Failed to create case database:', error);
        throw error;
    }
}

// 检查并添加缺失的银行流水表列
function addMissingBankColumns(caseDb, dataType, callback) {
    if (dataType !== 'bank') {
        callback();  // 如果不是银行流水，直接返回
        return;
    }

    // 银行流水表应该包含的列
    const expectedColumns = [
        'customer_name', 'id_number', 'account_card', 'phone_number',
        'account_name', 'bank_name', 'counterparty_id_number', 'counterparty_bank_code',
        'counterparty_bank_name', 'transaction_branch_name', 'teller_number',
        'debit_credit_flag', 'counterparty_name', 'counterparty_card', 'extra_data',
        'record_hash'  // 添加记录哈希字段用于去重
    ];

    // 查询表的当前列结构
    caseDb.all("PRAGMA table_info(bank_transactions)", [], (err, columns) => {
        if (err) {
            console.error('Failed to get table structure:', err.message);
            callback();
            return;
        }

        const existingColumns = columns.map(col => col.name);
        let completedColumns = 0;
        const totalColumns = expectedColumns.length;

        if (totalColumns === 0) {
            callback();
            return;
        }

        expectedColumns.forEach(column => {
            if (!existingColumns.includes(column)) {
                // 添加缺失的列
                const alterSql = `ALTER TABLE bank_transactions ADD COLUMN ${column} TEXT`;
                caseDb.run(alterSql, (err) => {
                    if (err) {
                        console.error(`Failed to add column ${column}:`, err.message);
                    } else {
                        console.log(`Successfully added column: ${column}`);
                    }

                    completedColumns++;
                    if (completedColumns === totalColumns) {
                        callback();
                    }
                });
            } else {
                completedColumns++;
                if (completedColumns === totalColumns) {
                    callback();
                }
            }
        });

        // 如果所有列都已存在
        if (expectedColumns.length === 0) {
            callback();
        }
    });
}

// 检查并添加缺失的 extra_data 列(通用函数,适用于所有数据类型表)
function addMissingExtraDataColumn(caseDb, tableName, callback) {
    if (!tableName) {
        callback();
        return;
    }

    // 查询表的当前列结构
    caseDb.all(`PRAGMA table_info(${tableName})`, [], (err, columns) => {
        if (err) {
            console.error(`Failed to get table structure for ${tableName}:`, err.message);
            callback();
            return;
        }

        const existingColumns = columns.map(col => col.name);

        // 检查是否已有 extra_data 列
        if (!existingColumns.includes('extra_data')) {
            // 添加 extra_data 列
            const alterSql = `ALTER TABLE ${tableName} ADD COLUMN extra_data TEXT`;
            caseDb.run(alterSql, (err) => {
                if (err) {
                    console.error(`Failed to add extra_data column to ${tableName}:`, err.message);
                } else {
                    console.log(`Successfully added extra_data column to ${tableName}`);
                }
                callback();
            });
        } else {
            // 列已存在,直接回调
            callback();
        }
    });
}

// 确保导入日志表存在
function ensureImportLogsTable(caseDb, callback) {
    // 检查是否已存在 import_logs 表
    caseDb.get("SELECT name FROM sqlite_master WHERE type='table' AND name='import_logs'", (err, row) => {
        if (err) {
            console.error('检查 import_logs 表时出错:', err.message);
            callback();
            return;
        }

        if (row) {
            // 表已存在，直接回调
            callback();
        } else {
            // 表不存在，创建它
            const createTableSql = `
                CREATE TABLE import_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_id INTEGER,
                    file_name TEXT,
                    file_hash TEXT,
                    data_type TEXT,
                    import_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(case_id, file_hash)
                )
            `;

            caseDb.run(createTableSql, (err) => {
                if (err) {
                    console.error('创建 import_logs 表失败:', err.message);
                } else {
                    console.log('Successfully created import_logs table');
                }
                callback();
            });
        }
    });
}

module.exports = {
    initDatabase,
    getDb,
    getCaseDatabase,
    createCaseDatabase,
    addMissingBankColumns,
    addMissingExtraDataColumn,
    ensureImportLogsTable
};
